use XCF;
/*SELECT * from sys_menu;
select * from sysobjects where xtype='U' and name like 'act_%';
select name from sysobjects where xtype='U' and name like 'act_%';
USE [XCF]*/
GO

/****** Object:  Table [dbo].[t_fin_fee_check]    Script Date: 2017/11/11 12:06:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/*账单收入明细表*/
CREATE TABLE [dbo].[t_fin_fee_check](
  [biz_id] [nvarchar](50) NOT NULL,/*业务编号*/
  [pay_dept] [nvarchar](50) NULL,/*结算单位*/
  [pay_dept_name] [nvarchar](50) NULL,
  [apply_dept] [nvarchar](50) NULL,/*申请部门*/
  [apply_dept_name] [nvarchar](50) NULL,
  [biz_type] [nvarchar](10) NULL,/*业务类型*/
  [biz_type_desc] [nvarchar](50) NULL,
  [ticket_category] [nvarchar](20) NULL,/*发票类型*/
  [ticket_name] [nvarchar](50) NULL,
  [fee_category] [nvarchar](50) NULL,/*费用类型*/
  [fee_category_desc] [nvarchar](50) NULL,
  [fee_desc] [nvarchar](50) NULL,/*费用项目*/
  [tax_ratio] [numeric](5, 2) NULL,/*税率*/
  [fee] [numeric](10, 2) NULL,/*未含税金额*/
  [fee_added] [numeric](8, 2) NULL,/*增值税*/
  [port_start] [nvarchar](50) NOT NULL,/*港口开始id*/
  [port_start_desc] [nvarchar](50) NULL,
  [port_end] [nvarchar](50) NULL,/*港口结算id*/
  [port_end_desc] [nvarchar](50) NULL,
  [crt_remark] [nvarchar](50) NULL,/*开票备注信息*/
  [apply_date] [datetime] NULL,/*申请时间*/
  [oper_date] [datetime] NULL,/*处理时间*/
  [apply_creator] [nvarchar](50) NULL,/*申请人id*/
  [apply_creator_name] [nvarchar](50) NULL,
  [operator] [nvarchar](50) NULL,/*承办人*/
  [operator_name] [nvarchar](50) NULL,
  [create_time] [datetime] NULL,
  status VARCHAR(2) NULL
) ON [PRIMARY]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'业务编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_fin_fee_check', @level2type=N'COLUMN',@level2name=N'biz_id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'结算单位' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_fin_fee_check', @level2type=N'COLUMN',@level2name=N'pay_dept'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'起止港' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_fin_fee_check', @level2type=N'COLUMN',@level2name=N'port_start'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'开票备注信息' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N't_fin_fee_check', @level2type=N'COLUMN',@level2name=N'crt_remark'
GO

INSERT INTO [dbo].[t_fin_fee_check](
   [biz_id]
  ,[pay_dept]
  ,[pay_dept_name]
  ,[apply_dept]
  ,[apply_dept_name]
  ,[biz_type]
  ,[biz_type_desc]
  ,[ticket_category]
  ,[ticket_name]
  ,[fee_category]
  ,[fee_category_desc]
  ,[fee_desc]
  ,[tax_ratio]
  ,[fee]
  ,[fee_added]
  ,[port_start]
  ,[port_start_desc]
  ,[port_end]
  ,[port_end_desc]
  ,[crt_remark]
  ,[oper_date]
  ,[apply_creator]
  ,[apply_creator_name]
  ,[operator]
  ,[operator_name]
  ,[create_time])
VALUES
  ('bizid1'
    ,'pay_dept_id1'
    ,'支付部门name1'
    ,'申请部门code'
    ,'申请部门名称1'
    ,'biz_type'
    ,'业务类型name1'
    ,'发票类型'
    ,'发票名目'
    ,'费用类型'
    ,'费用项目code'
    ,'费用项目'
    ,'0.01'
    ,11111
    ,121
    ,'起始港口'
    ,'起始港口名称1'
    ,'终点港口'
    ,'终点港口名称1'
    ,'开票备注信息12'
    ,SYSDATETIME()
    ,'0011'
    ,'申请人名称12'
    ,'0012'
    ,'承办人名称1'
    ,SYSDATETIME()),
  ('bizid2'
    ,'pay_dept_id2'
    ,'支付部门name2'
    ,'申请部门code2'
    ,'申请部门名称2'
    ,'biz_type'
    ,'业务类型name2'
    ,'发票类型2'
    ,'发票名目2'
    ,'费用类型2'
    ,'费用项目code2'
    ,'费用项目2'
    ,'0.02'
    ,11111
    ,121
    ,'起始港口'
    ,'起始港口名称2'
    ,'终点港口'
    ,'终点港口名称2'
    ,'开票备注信息12'
    ,SYSDATETIME()
    ,'0011'
    ,'申请人名称12'
    ,'0012'
    ,'承办人名称1'
    ,SYSDATETIME()
  )
GO
ALTER TABLE t_fin_fee_check ADD id INT NOT NULL IDENTITY;
ALTER TABLE t_fin_fee_check ADD CONSTRAINT t_fin_fee_check_code_pk PRIMARY KEY (id);

 /*账单-成本明细表*/
 SELECT * into t_fin_fee_outcome from t_fin_fee_check;

--drop TABLE [dbo].[t_fin_fee_record];
/*待处理账单-主表*/
CREATE TABLE [dbo].[t_fin_fee_record](
  id int not NULL  IDENTITY,
  [biz_id] [nvarchar](50) NOT NULL,/*业务编号*/
  [biz_type] [nvarchar](10) NULL,/*业务类型*/
  [biz_type_desc] [nvarchar](50) NULL,

  [apply_dept] [nvarchar](50) NULL,/*申请部门*/
  [apply_dept_name] [nvarchar](50) NULL,
  [apply_date] [datetime] NULL,/*申请时间*/
  [apply_creator] [nvarchar](50) NULL,/*申请人id*/
  [apply_creator_name] [nvarchar](50) NULL,

  [pay_dept] [nvarchar](50) NULL,/*结算单位*/
  [pay_dept_name] [nvarchar](50) NULL,

  [ticket_category] [nvarchar](20) NULL,/*发票类型*/
  [ticket_name] [nvarchar](50) NULL,
  [fee_category] [nvarchar](50) NULL,/*费用类型*/
  [fee_category_desc] [nvarchar](50) NULL,
  [fee_desc] [nvarchar](50) NULL,/*费用项目*/
  [tax_ratio] [numeric](5, 2) NULL,/*税率*/
  [fee] [numeric](10, 2) NULL,/*未含税金额*/
  [fee_added] [numeric](8, 2) NULL,/*增值税*/
  fee_ratio_id int,/*关联税率*/

  [operator] [nvarchar](50) NULL,/*申请受理人*/
  [operator_name] [nvarchar](50) NULL,
  [create_time] [datetime] NULL,
  status nVARCHAR(2) NULL /*申请单状态*/
) ON [PRIMARY]
GO
